﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Helpers;

namespace SilverPOP.Data.Process.DAL
{
    public class DAL
    {
        #region Properties
        public static String _connectionString;

        public static String ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_connectionString))
                {
                    return ConfigHelpers.SQLConnectionString;
                }
                else
                {
                    return _connectionString;
                }
            }
            set
            {
                _connectionString = value;
            }
        }
        #endregion

        #region Get Data By Using  ADO.NET SqlDataReader






        public static List<DataReaderObject> ExecuteStoredProcedureAndGetDataObject(String storedProcedureName)
        {
            SqlCommand sqlCommand = GetSQLCommandObject();


            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = storedProcedureName;
            sqlCommand.CommandTimeout = 3000;

            sqlCommand.Connection.Open();

            SqlDataReader sqlReader = sqlCommand.ExecuteReader();

            if (sqlReader != null && sqlReader.HasRows)
            {
                List<DataReaderObject> dataList = new List<DataReaderObject>();
                CreateDataObjects(sqlReader, dataList);
                sqlCommand.Connection.Close();
                return dataList.Count > 0 ? dataList : null;
            }
            else
            {
                sqlCommand.Connection.Close();
                return null;
            }
        }

        public static List<DataReaderObject> ExecuteStoredProcedureWithParametersAndGetDataObject(String storedProcedureName, List<ParamWithValue> paramValueList)
        {
            SqlCommand sqlCommand = GetSQLCommandObject();


            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = storedProcedureName;
            sqlCommand.CommandTimeout = 3000;

            if (paramValueList != null && paramValueList.Count > 0)
            {
                foreach (ParamWithValue paramWithValue in paramValueList)
                {
                    sqlCommand.Parameters.AddWithValue(paramWithValue.ParamName, paramWithValue.ParamValue);
                }
            }

            sqlCommand.Connection.Open();

            SqlDataReader sqlReader = sqlCommand.ExecuteReader();

            if (sqlReader != null && sqlReader.HasRows)
            {
                List<DataReaderObject> dataList = new List<DataReaderObject>();
                CreateDataObjects(sqlReader, dataList);
                sqlCommand.Connection.Close();
                return dataList.Count > 0 ? dataList : null;
            }
            else
            {
                sqlCommand.Connection.Close();
                return null;
            }
        }

        public static List<DataReaderObject> ExecuteInLineQueryAndGetDataObject(String queryText)
        {
            SqlCommand sqlCommand = GetSQLCommandObject();


            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = queryText;
            sqlCommand.CommandTimeout = 3000;

            sqlCommand.Connection.Open();

            SqlDataReader sqlReader = sqlCommand.ExecuteReader();

            if (sqlReader != null && sqlReader.HasRows)
            {
                List<DataReaderObject> dataList = new List<DataReaderObject>();
                CreateDataObjects(sqlReader, dataList);
                sqlCommand.Connection.Close();
                return dataList.Count > 0 ? dataList : null;
            }
            else
            {
                sqlCommand.Connection.Close();
                return null;
            }
        }


        private static SqlCommand GetSQLCommandObject()
        {
            SqlConnection sqlConn = new SqlConnection();
            sqlConn.ConnectionString = ConnectionString;

            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;

            return sqlComm;

        }

        private static void CreateDataObjects(SqlDataReader sqlReader, List<DataReaderObject> dataList)
        {
            while (sqlReader.Read())
            {
                DataReaderObject rowColumnValue = new DataReaderObject();

                //Get the columns and their  value of a row
                for (int counter = 0; counter < sqlReader.FieldCount; counter++)
                {
                    rowColumnValue.ColumnValueList.Add(sqlReader.GetValue(counter));
                }

                dataList.Add(rowColumnValue);
            }
        }

        public static void SetConnectionString(string source, string databaseName, string userName, string password)
        {
            ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", source, databaseName, userName, password);
        }

        public static void SetDefautConnectionString()
        {
            ConnectionString = string.Empty;
        }


        #endregion

        #region Get Data By Using ADO.NET SQLDataAdapter

        public static DataTable ExecuteStoredProcedureAndGetDataTable(String storedProcedureName)
        {
            SqlDataAdapter sqlAdapter = GetDataAdapterObject();

            DataTable dbTable = new DataTable();

            sqlAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            sqlAdapter.SelectCommand.CommandText = storedProcedureName;
            sqlAdapter.SelectCommand.CommandTimeout = 3000;

            sqlAdapter.Fill(dbTable);

            if (dbTable != null && dbTable.Rows.Count > 0)
            {
                return dbTable;
            }
            else
            {
                return null;
            }
        }


        public static DataTable ExecuteStoredProcedureWithParametersAndGetDataTable(String storedProcedureName, List<ParamWithValue> paramValueList)
        {
            SqlDataAdapter sqlAdapter = GetDataAdapterObject();

            DataTable dbTable = new DataTable();

            sqlAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            sqlAdapter.SelectCommand.CommandText = storedProcedureName;
            sqlAdapter.SelectCommand.CommandTimeout = 3000;

            if (paramValueList != null && paramValueList.Count > 0)
            {
                foreach (ParamWithValue paramWithValue in paramValueList)
                {
                    sqlAdapter.SelectCommand.Parameters.AddWithValue(paramWithValue.ParamName, paramWithValue.ParamValue);
                }
            }

            sqlAdapter.Fill(dbTable);

            if (dbTable != null && dbTable.Rows.Count > 0)
            {
                return dbTable;
            }
            else
            {
                return null;
            }
        }

        public static DataTable ExecuteInLineQueryAndGetDataTable(String queryText)
        {
            SqlDataAdapter sqlAdapter = GetDataAdapterObject();

            DataTable dbTable = new DataTable();

            sqlAdapter.SelectCommand.CommandType = CommandType.Text;
            sqlAdapter.SelectCommand.CommandText = queryText;
            sqlAdapter.SelectCommand.CommandTimeout = 3000;

            sqlAdapter.Fill(dbTable);

            if (dbTable != null && dbTable.Rows.Count > 0)
            {
                return dbTable;
            }
            else
            {
                return null;
            }
        }


        private static SqlDataAdapter GetDataAdapterObject()
        {

            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            sqlAdapter.SelectCommand = new SqlCommand();
            sqlAdapter.SelectCommand.Connection = new SqlConnection();
            sqlAdapter.SelectCommand.Connection.ConnectionString = ConnectionString;

            return sqlAdapter;

        }


        #endregion

        #region Update Delete Methods Using SP Or InLine Query
        public static bool UpdateOrDeleteOrInsertDataUsingSP(String storedProcedureName, List<ParamWithValue> paramValueList)
        {
            SqlCommand sqlCommand = GetSQLCommandObject();


            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = storedProcedureName;
            sqlCommand.CommandTimeout = 3000;

            if (paramValueList != null && paramValueList.Count > 0)
            {
                foreach (ParamWithValue paramWithValue in paramValueList)
                {
                    sqlCommand.Parameters.AddWithValue(paramWithValue.ParamName, paramWithValue.ParamValue);
                }
            }

            sqlCommand.Connection.Open();

            int result = sqlCommand.ExecuteNonQuery();

            sqlCommand.Connection.Close();

            return result > 0 ? true : false;

        }

        public static void UpdateOrDeleteOrInsertDataUsingInLineQuery(String queryText)
        {
            SqlCommand sqlCommand = GetSQLCommandObject();

            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = queryText;
            sqlCommand.CommandTimeout = 3000;

            sqlCommand.Connection.Open();

            int result = sqlCommand.ExecuteNonQuery();

            sqlCommand.Connection.Close();

        }

        #endregion

        #region Check Database Connection String

        public static bool CheckConnectionString(String connectionString)
        {
            SqlConnection sqlConn = new SqlConnection();
            sqlConn.ConnectionString = connectionString;

            sqlConn.Open();
            sqlConn.Close();

            return true;
        }

        #endregion


    }//end class
}
